Examples > Excluding Database Objects |
This example demonstrates how to exclude specific database objects from the comparison. In general, to exclude an object, do the followings:
using System; using System.Collections.Generic; using System.Text; using xSQL.Schema.Core; using xSQL.Schema.SqlServer; using xSQL.SchemaCompare.SqlServer; namespace xSQL.Sdk.SchemaCompare.Examples { class DatabaseCompareWithExclusion { public static void CompareWithExclusion() { SqlServer server; SqlDatabase xDatabase, yDatabase; SqlSchemaCompare comparer; ScriptManager sqlScript; ScriptExecutionStatusEnum status; SqlTablePair pair; try { // create the SQL Server object server = new SqlServer(@"(local)"); // create the left database xDatabase = server.GetDatabase("Source"); // create the right database yDatabase = server.GetDatabase("Target"); // create the comparer comparer = new SqlSchemaCompare(xDatabase, yDatabase); // attach event handlers to these events in order to get some progress information during the schema read and compare comparer.LeftDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation); comparer.RightDatabase.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation); comparer.SchemaOperation += new EventHandler<SchemaOperationEventArgs>(database_SchemaOperation); // use the generic event EntityPairingFinished to exclude an object from the comparison or // the SqlTablePairingFinished event which is raised specifically for database tables. // the table-specific event performs slightly better since it has to check only tables and not every object in the database // objects are usually excluded after the pairing is finished. comparer.SqlTablePairingFinished += new EventHandler<SqlTablePairEventArgs>(comparer_SqlTablePairingFinished); // step 1: read the schema comparer.ReadSchema(); // step 2: pair the database objects comparer.PairObjects(); // this is another way of an excluding an object; it should be done always after the pairing finishes pair = comparer.SqlTablePairs["employees"]; if (pair != null) pair.Included = false; // step 3: compare the schema comparer.Compare(); // check for errors that could have occurred during the schema compare. if (ErrorRepository.Instance.HasErrors()) { Console.WriteLine("Some errors occurred during the database compare"); Console.Write(ErrorRepository.Instance.GetErrors()); } // check the database status; exit if no schema differences are found. if (comparer.SqlDatabasePair.ComparisonStatus == ComparisonStatusEnum.Equal) return; // step 4: get the T-SQL script intended for the right database; that is the script that should be executed // on Target database in order to make it the same as the Source database sqlScript = comparer.GetRightDatabaseScript(); if (!sqlScript.IsEmpty()) { // use this event to get some progress during the script execution sqlScript.SchemaScriptExecuting += new EventHandler<SchemaScriptEventArgs>(sqlScript_SchemaScriptExecuting); // execute the synchronization script status = sqlScript.Execute(); // check the status if (status == ScriptExecutionStatusEnum.Succeeded) { Console.WriteLine("Database synchronization succeeded."); } else if (status == ScriptExecutionStatusEnum.Canceled) { Console.WriteLine("Database synchronization was canceled."); } else { //--check for silent errors if (ErrorRepository.Instance.HasErrors()) { Console.WriteLine("Some errors occurred during the script execution."); Console.Write(ErrorRepository.Instance.GetErrors()); } } } } catch (Exception ex) { Console.Write(ex.ToString()); } } private static void database_SchemaOperation(object sender, SchemaOperationEventArgs e) { //--exclude verbose messages if (e.Message.MessageType != OperationMessageTypeEnum.Verbose) Console.WriteLine(e.Message.Text); } private static void sqlScript_SchemaScriptExecuting(object sender, SchemaScriptEventArgs e) { Console.WriteLine("{0} {1}", DateTime.Now.ToString("HH:mm:ss"), e.Script); } private static void comparer_SqlTablePairingFinished(object sender, SqlTablePairEventArgs e) { if (e.Pair.ContainsMember("employees", SqlEntityTypeEnum.Table)) e.Pair.Included = false; } } }